Building Inventory and Supply Chain Dashboards in Microsoft Power BI

Course: Microsoft Power BI Training

Topic: Building Inventory and Supply Chain Dashboards

Course Link: Microsoft Power BI Training at JustAcademy

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Inventory and Supply Chain Dashboards

Inventory and supply chain dashboards are interactive business intelligence reports that help organizations monitor stock levels, product movement, supplier performance, purchasing activities, warehouse operations, order fulfillment, and delivery performance. Microsoft Power BI enables users to connect operational data from different sources, transform it using Power Query, create analytical calculations using DAX, and present the results through interactive visualizations.

Organizations need accurate inventory information to maintain sufficient stock, avoid shortages, reduce excess inventory, and fulfill customer orders on time. Supply chain dashboards combine information from purchasing, warehousing, sales, suppliers, and logistics to provide a consolidated view of operational performance.

For example, a retail company may have thousands of products across multiple warehouses. Without a centralized dashboard, managers may struggle to identify products running below their reorder levels, suppliers delivering late, warehouses holding excess stock, or products experiencing increasing demand.

Power BI helps bring this information together so managers can identify operational problems, investigate their causes, and make informed decisions.

1.1 What Is an Inventory Dashboard?

An inventory dashboard is a visual reporting solution that displays information about the quantity, value, movement, availability, and status of inventory.

Common inventory metrics include:

• Total Stock on Hand
• Available Inventory
• Inventory Value
• Opening and Closing Stock
• Incoming and Outgoing Stock
• Stockout Count
• Products Below Reorder Level
• Overstocked Products
• Inventory Turnover Ratio
• Days of Inventory on Hand
• Slow-Moving and Non-Moving Inventory

1.2 What Is a Supply Chain Dashboard?

A supply chain dashboard monitors the movement of products, materials, and orders from suppliers through purchasing, storage, distribution, and final delivery.

Typical supply chain metrics include:

• Supplier Delivery Performance
• Purchase Order Status
• Supplier Lead Time
• Order Fulfillment Rate
• On-Time Delivery Percentage
• Order Backlog
• Transportation Costs
• Warehouse Performance
• Demand Forecast Accuracy
• Supply and Demand Balance

1.3 Difference Between Inventory and Supply Chain Dashboards

2. Learning Objectives

After completing this topic, learners should be able to:

• Understand inventory management and supply chain analytics.
• Identify appropriate inventory and supply chain KPIs.
• Import operational data into Power BI.
• Clean and transform inventory and purchasing data using Power Query.
• Build a suitable data model for inventory analysis.
• Create DAX measures for stock, inventory value, and stock movement.
• Calculate inventory turnover and days of inventory on hand.
• Monitor stockouts, overstocking, and reorder requirements.
• Analyze supplier performance and delivery lead times.
• Compare demand, supply, and fulfillment performance.
• Design interactive dashboards using charts, cards, matrices, and slicers.
• Validate operational calculations and reconcile inventory balances.
• Publish, secure, and maintain Power BI dashboards.

3. Understanding Inventory Management

3.1 What Is Inventory?

Inventory refers to the goods, materials, and products an organization holds for production, sale, distribution, or operational use.

Inventory can include:

• Raw Materials: Materials used to manufacture finished products.
• Work in Progress: Items currently being manufactured or processed.
• Finished Goods: Completed products ready for sale or distribution.
• Maintenance Supplies: Items used to maintain equipment and facilities.
• Packaging Materials: Boxes, labels, containers, and related supplies.
• Spare Parts: Components held for equipment repair or replacement.

3.2 Inventory Stock Levels

For a simple inventory movement, closing stock can be calculated as opening stock plus receipts minus issues. Real inventory systems may also require adjustments for returns, transfers, damaged goods, write-offs, production consumption, and stock corrections.

3.3 Why Inventory Monitoring Is Important

• Prevents stock shortages and lost sales.
• Reduces excess stock and storage expenses.
• Helps maintain the right inventory across locations.
• Improves purchasing and replenishment decisions.
• Supports demand planning and production scheduling.
• Identifies slow-moving or obsolete products.
• Improves inventory valuation and working capital management.

4. Understanding Supply Chain Management

4.1 What Is Supply Chain Management?

Supply chain management involves planning, coordinating, and controlling the flow of products, information, and resources from suppliers to customers. It includes procurement, production, inventory management, warehousing, transportation, and order fulfillment.

4.2 Major Supply Chain Activities

4.3 Role of Power BI in Supply Chain Analytics

Power BI combines data from different operational systems into a unified reporting environment. It can help users compare supplier performance, investigate warehouse issues, monitor stock movements, evaluate purchasing trends, and identify supply chain bottlenecks.

Inventory and supply chain visibility dashboards can also be used to compare supply and demand, locate products at risk of shortage, and identify inventory that may be excessive. Microsoft provides an example of these capabilities in its Inventory Visibility Power BI dashboard documentation.

5. Data Sources for Inventory and Supply Chain Dashboards

5.1 Common Data Sources

• Microsoft Excel: Inventory sheets, stock registers, purchase records, and supplier lists.
• SQL Server: Transaction histories, warehouse records, sales orders, and inventory movements.
• ERP Systems: Purchase orders, goods receipts, inventory balances, and production transactions.
• Warehouse Management Systems: Bin locations, picking records, warehouse transfers, and stock counts.
• CRM Systems: Customer orders, sales forecasts, and demand-related information.
• Procurement Applications: Supplier records, purchase orders, quotations, and delivery schedules.
• Logistics Platforms: Shipment tracking, delivery status, carrier information, and transportation costs.
• CSV Files: Exported inventory reports and operational datasets.

5.2 Example Inventory Dataset

5.3 Example Supplier Dataset

6. Importing and Cleaning Data Using Power Query

6.1 Importing Inventory Data

1. Open Power BI Desktop.
2. Select Home > Get Data.
3. Choose the appropriate data source, such as Excel, SQL Server, or CSV.
4. Select the inventory, product, supplier, and purchase order tables.
5. Select Transform Data to open Power Query Editor.
6. Inspect the data structure and identify data-quality issues.
7. Apply the required transformations.
8. Select Close & Apply to load the prepared data.

6.2 Cleaning Inventory Data

Inventory dashboards are only reliable when stock records and transaction data are accurate. Common data-cleaning activities include:

• Removing unnecessary columns.
• Standardizing product and supplier names.
• Checking duplicate product and transaction identifiers.
• Converting quantities to numeric data types.
• Converting transaction and delivery dates to date data types.
• Handling missing product IDs and warehouse IDs.
• Checking for negative quantities and unexpected stock balances.
• Identifying cancelled purchase orders and reversed transactions.
• Standardizing delivery statuses.
• Validating unit costs and currency values.

Do not delete repeated product IDs from transaction tables automatically. A product can legitimately appear in many transactions. Remove duplicates only when the business key and the data meaning establish that a record is duplicated.

6.3 Handling Missing Values

Missing data should be handled according to business meaning. A missing supplier name may be mapped to an approved Unknown category, while a missing quantity may require investigation before it can be used in calculations.

• Investigate missing stock quantities.
• Validate missing product and warehouse identifiers.
• Do not replace unknown quantities with zero without an approved rule.
• Review missing delivery dates before calculating lead times.
• Document assumptions applied during data cleaning.

6.4 Creating a Date Table

A dedicated date table supports consistent inventory movement, purchasing, sales, and delivery trend analysis.

DimDate =
ADDCOLUMNS(
    CALENDAR(
        DATE(2024, 1, 1),
        DATE(2027, 12, 31)
    ),
    "Year", YEAR([Date]),
    "Month Number", MONTH([Date]),
    "Month", FORMAT([Date], "MMM"),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "Year Month", FORMAT([Date], "yyyy-MM")
)

Adjust the date range for the project. Mark the table as a date table and sort the Month column by Month Number. If a model uses several date fields, such as Order Date and Delivery Date, determine which date relationship each measure should use.

7. Building the Inventory and Supply Chain Data Model

7.1 Understanding Fact and Dimension Tables

A well-designed Power BI model separates measurable business events from descriptive information. A star schema is a useful design for many inventory and supply chain reporting solutions.

7.2 Example Data Model

                 DimProduct
                     |
                     |
DimDate ------ FactInventorySnapshot ------ DimWarehouse
                     |
                     |
                 DimSupplier

DimDate ------ FactPurchaseOrders --------- DimSupplier
                     |
                     |
                 DimProduct

This is a conceptual illustration. The actual relationships depend on the grain and source structure of the data. A current inventory snapshot may not contain SupplierID directly, so supplier filtering may need to occur through a purchasing or product-sourcing model.

7.3 Understanding Data Grain

Data grain describes what one row represents. It must be clearly defined before tables are related or measures are written.

Examples include:

• One row per product per warehouse per snapshot date.
• One row per inventory movement transaction.
• One row per purchase order line.
• One row per sales order line.
• One row per supplier delivery.

Mixing different grains without suitable relationships or aggregation logic can cause duplicated quantities and incorrect totals. For example, joining a product-level inventory balance to multiple purchase order lines can repeat the inventory balance for each matching purchase order.

7.4 Relationship Best Practices

• Use unique keys on the one side of one-to-many relationships.
• Ensure related columns have compatible data types.
• Prefer clear dimension-to-fact relationships.
• Use single-direction filtering by default where appropriate.
• Avoid unnecessary many-to-many relationships.
• Handle multiple date relationships deliberately.
• Test totals after creating or modifying relationships.

8. Key Inventory KPIs

Key Performance Indicators (KPIs) summarize inventory health and help users identify stock problems and opportunities for improvement.

9. Creating Inventory Measures Using DAX

9.1 Total Closing Stock

Assume the inventory snapshot table contains one row per product, warehouse, and snapshot date.

Total Closing Stock =
SUM(FactInventorySnapshot[ClosingStock])

This measure sums the closing stock values in the current filter context. If the table contains multiple historical snapshots, summing every snapshot may produce an incorrect current stock total. Filter to the relevant snapshot date or use a measure that explicitly retrieves the latest valid snapshot.

9.2 Total Opening Stock

Total Opening Stock =
SUM(FactInventoryMovement[OpeningStock])

Use this formula only if the source table stores opening stock at a grain that can be safely aggregated. Opening balances should not be repeated across transaction rows.

9.3 Total Incoming Stock

Total Incoming Stock =
SUM(FactInventoryMovement[ReceivedQty])

This measure sums received quantities under the current filters. Define whether it includes returns, warehouse transfers, production receipts, and adjustments.

9.4 Total Outgoing Stock

Total Outgoing Stock =
SUM(FactInventoryMovement[IssuedQty])

The outgoing quantity may represent sales, production consumption, internal transfers, or other inventory issues. Decide which movement types should be included in each report.

9.5 Calculating Inventory Movement Balance

Calculated Closing Stock =
[Total Opening Stock]
    + [Total Incoming Stock]
    - [Total Outgoing Stock]

This is a simplified stock reconciliation. If there are stock adjustments, returns, transfers, or other movement types, include them according to the approved inventory accounting rules.

9.6 Inventory Value

If each product has a reliable unit cost and the inventory table contains a suitable current quantity, a simple valuation measure can use SUMX.

Inventory Value =
SUMX(
    FactInventorySnapshot,
    FactInventorySnapshot[ClosingStock]
        * FactInventorySnapshot[UnitCost]
)

This example assumes each row's unit cost is valid for its inventory quantity and that the table does not duplicate the same stock balance. For official financial reporting, use the organization's approved valuation method, such as moving average, FIFO, or standard cost, as applicable.

9.7 Count of Stockout Products

Stockout Products =
CALCULATE(
    DISTINCTCOUNT(DimProduct[ProductID]),
    FILTER(
        FactInventorySnapshot,
        FactInventorySnapshot[ClosingStock] <= 0
    )
)

This measure identifies distinct products with zero or negative stock in the current filter context. If the report contains multiple warehouses, decide whether a stockout means a product is unavailable in a specific warehouse or unavailable across all warehouses. A product with no stock in one warehouse may still be available elsewhere.

9.8 Products Below Reorder Level

Products Below Reorder Level =
COUNTROWS(
    FILTER(
        VALUES(DimProduct[ProductID]),
        CALCULATE([Total Closing Stock])
            < CALCULATE(
                MAX(DimProduct[ReorderLevel])
            )
    )
)

This example assumes that the product's reorder level is consistently defined and that stock is evaluated at the appropriate warehouse or product-location level. If reorder levels vary by warehouse, model the threshold at that level rather than using one global product value.

9.9 Inventory Turnover Ratio

Inventory turnover measures how often inventory is sold or consumed relative to the average inventory held during a period. For financial reporting, the standard formula uses cost of goods sold and average inventory value.

Inventory Turnover =
DIVIDE(
    [Cost of Goods Sold],
    [Average Inventory Value],
    BLANK()
)

Inventory turnover is generally interpreted over a defined period, such as a quarter or year. Ensure the numerator and denominator use consistent time periods and valuation definitions. A quantity-based turnover measure may also be useful operationally, but it is a different metric.

9.10 Days of Inventory on Hand

Days of Inventory on Hand =
DIVIDE(
    [Average Inventory Value],
    [Cost of Goods Sold],
    BLANK()
) * [Days in Reporting Period]

For a 365-day annual reporting period, Days in Reporting Period may be 365; for a quarter, use the appropriate number of days. This calculation is meaningful when average inventory value and cost of goods sold use compatible valuation bases.

9.11 Average Inventory Value

A simple average of opening and closing inventory can be used when those balances reasonably represent the period:

Average Inventory Value =
DIVIDE(
    [Opening Inventory Value]
        + [Closing Inventory Value],
    2,
    BLANK()
)

For highly seasonal businesses or volatile inventory, averaging more frequent snapshots may provide a more representative result.

10. Reorder Planning and Safety Stock

10.1 What Is a Reorder Point?

A reorder point is the inventory threshold that signals when replenishment should be initiated. It helps prevent stockouts while accounting for supplier lead time and demand.

A common simplified formula is:

Reorder Point =
Average Daily Demand * Lead Time in Days
    + Safety Stock

For example, if average daily demand is 20 units, supplier lead time is 5 days, and safety stock is 30 units, the reorder point is 130 units.

10.2 What Is Safety Stock?

Safety stock is additional inventory held to protect against uncertainty in demand, delivery time, and supply availability.

Safety stock decisions may consider:

• Demand variability.
• Supplier lead-time variability.
• Target service level.
• Product criticality.
• Cost of stockouts.
• Storage and holding costs.

10.3 Suggested Reorder Quantity

A simplified operational calculation can estimate how much stock is required to reach a target level.

Suggested Reorder Quantity =
MAX(
    0,
    Target Stock Level - Available Inventory
)

This is an illustrative replenishment rule, not a universal purchasing recommendation. A production-ready calculation should consider open purchase orders, reserved stock, backorders, supplier minimum order quantities, order multiples, expected receipts, and the organization's replenishment policy.

10.4 Creating a Reorder Status

A calculated column or a suitable measure can classify inventory based on a defined threshold. For a simple row-level example:

Stock Status =
IF(
    FactInventorySnapshot[ClosingStock] <=
        FactInventorySnapshot[ReorderLevel],
    "Reorder Required",
    "Stock Available"
)

This example assumes each row contains the correct stock balance and reorder threshold for the same product-location-date combination. A measure-based classification may be more suitable when users need the status to respond dynamically to slicers.

11. Key Supply Chain KPIs

12. Creating Supply Chain Measures Using DAX

12.1 Total Purchase Order Value

Total Purchase Order Value =
SUM(FactPurchaseOrders[PurchaseOrderValue])

Decide whether the measure includes draft, open, cancelled, or completed orders. For financial reporting, the purchase order value should not be confused with actual invoiced or received cost.

12.2 Average Supplier Lead Time

If the source contains a prevalidated numeric LeadTimeDays column:

Average Supplier Lead Time =
AVERAGE(FactPurchaseOrders[LeadTimeDays])

Lead time should use a clearly defined start and end event, such as order placement to receipt. Consider whether to exclude cancelled orders, incomplete deliveries, and invalid dates.

12.3 Calculating Lead Time from Dates

If each row represents one completed delivery and contains the relevant dates, lead time can be calculated as a calculated column:

Lead Time Days =
DATEDIFF(
    FactPurchaseOrders[OrderDate],
    FactPurchaseOrders[ActualDeliveryDate],
    DAY
)

Validate missing dates and ensure the calculation uses the correct business definition. For partial deliveries, the model may need one row per receipt or delivery event rather than one row per purchase order.

12.4 On-Time Delivery Percentage

On-Time Deliveries =
CALCULATE(
    DISTINCTCOUNT(FactPurchaseOrders[PurchaseOrderID]),
    FILTER(
        FactPurchaseOrders,
        FactPurchaseOrders[ActualDeliveryDate]
            <= FactPurchaseOrders[ExpectedDeliveryDate]
    )
)

Eligible Deliveries =
CALCULATE(
    DISTINCTCOUNT(FactPurchaseOrders[PurchaseOrderID]),
    FILTER(
        FactPurchaseOrders,
        NOT ISBLANK(
            FactPurchaseOrders[ActualDeliveryDate]
        )
    )
)

On-Time Delivery % =
DIVIDE(
    [On-Time Deliveries],
    [Eligible Deliveries],
    BLANK()
)

This example assumes one eligible completed delivery per purchase order. If a purchase order contains multiple lines or partial shipments, calculate delivery performance at the correct line, shipment, or receipt grain. The expected date should reflect the agreed promised-date rule.

12.5 Order Fill Rate

Order Fill Rate % =
DIVIDE(
    SUM(FactOrderLines[FulfilledQty]),
    SUM(FactOrderLines[OrderedQty]),
    BLANK()
)

This formula measures quantity fulfillment. It is not the same as the percentage of orders fulfilled completely. For complete-order performance, count eligible orders whose required quantities were all fulfilled according to the chosen rule.

12.6 Backorder Quantity

Backorder Quantity =
SUM(FactOrderLines[OrderedQty])
    - SUM(FactOrderLines[FulfilledQty])

This simplified measure assumes that fulfilled quantity does not exceed ordered quantity and that cancelled lines, returns, and adjustments have been handled appropriately.

12.7 Forecast Error

A simple signed forecast error is calculated as forecast minus actual demand:

Forecast Error =
[Forecast Quantity] - [Actual Demand]

A positive result means the forecast exceeded actual demand, while a negative result means the forecast was below actual demand under this sign convention.

12.8 Absolute Forecast Error

Absolute Forecast Error =
ABS([Forecast Error])

Absolute error shows the size of the difference without considering its direction. Forecast accuracy should be calculated using a documented method appropriate for the business, especially when demand can be zero or very small.

13. Designing the Inventory Dashboard

13.1 Inventory Overview Page

The inventory overview page provides a summary of the current stock position.

Recommended KPI cards include:

• Total Stock on Hand.
• Total Inventory Value.
• Stockout Products.
• Products Below Reorder Level.
• Overstocked Products.
• Inventory Turnover.

Recommended visuals include:

• Bar chart showing stock by product category.
• Column chart comparing stock across warehouses.
• Line chart showing inventory trends over time.
• Matrix showing products, stock, reorder level, and stock status.
• Table highlighting products requiring replenishment.

13.2 Inventory Movement Page

This page helps users understand how stock changes over time.

Useful metrics and visuals include:

• Opening stock, receipts, issues, and closing stock.
• Monthly incoming and outgoing quantities.
• Transfers between warehouses.
• Inventory adjustments and returns.
• Stock movement by product category.

Movement reporting should distinguish physical stock movement from changes in valuation. A transfer between warehouses may change the location balance without changing the organization's total stock.

13.3 Stock Replenishment Page

This page identifies products that may require purchasing or internal stock transfers.

Recommended fields include:

• Product Name.
• Warehouse.
• Available Inventory.
• Reorder Point.
• Safety Stock.
• Open Purchase Order Quantity.
• Expected Receipt Date.
• Suggested Reorder Quantity.

Prioritize products based on shortage risk, product importance, expected demand, and supplier lead time rather than stock quantity alone.

13.4 Slow-Moving and Overstock Analysis

Slow-moving inventory consists of items that move or sell more slowly than expected. Overstock occurs when inventory exceeds an appropriate requirement or target.

Recommended visuals include:

• Bar chart showing inventory value by aging category.
• Table of products with low recent movement.
• Matrix showing stock value by category and warehouse.
• Scatter chart comparing stock value and recent demand.

Define aging periods and slow-moving rules with the business. A product with no recent sales may still be necessary for maintenance, seasonal demand, or contractual obligations.

14. Designing the Supply Chain Dashboard

14.1 Supply Chain Overview Page

This page summarizes overall supply chain health and service performance.

Recommended KPIs include:

• Total Purchase Order Value.
• Open Purchase Orders.
• Average Supplier Lead Time.
• On-Time Delivery Percentage.
• Order Fill Rate.
• Backorder Quantity.
• Transportation Cost.

14.2 Supplier Performance Page

This page compares suppliers based on reliability, cost, quality, and delivery performance.

Recommended visuals include:

• Bar chart ranking suppliers by on-time delivery rate.
• Column chart comparing purchase order value by supplier.
• Table displaying supplier lead time and late deliveries.
• Scatter chart comparing lead time with delivery reliability.
• Trend chart showing supplier performance over time.

Supplier rankings should use sufficient context. A supplier with a small number of orders may have a high delivery rate that is less reliable than a supplier evaluated across hundreds of deliveries.

14.3 Purchase Order Tracking Page

Purchase order tracking helps procurement teams identify orders that are open, overdue, partially received, completed, or cancelled.

Useful fields include:

• Purchase Order ID.
• Supplier Name.
• Order Date.
• Expected Delivery Date.
• Received Quantity.
• Outstanding Quantity.
• Purchase Order Status.

14.4 Warehouse Performance Page

This page compares stock levels and operational performance across warehouses.

Useful indicators include:

• Stock quantity and value by warehouse.
• Warehouse capacity utilization.
• Stockout products by location.
• Inventory transfers.
• Receiving and dispatch volumes.
• Picking accuracy and order processing time, where available.

14.5 Demand Planning Page

The demand planning page compares historical demand, forecasts, and available supply to support replenishment decisions.

Useful visuals include:

• Line chart comparing actual demand and forecast demand.
• Column chart showing forecast error by product category.
• Table listing products with projected shortages.
• Matrix showing demand by month and product.
• Cards showing forecast accuracy and open supply quantities.

Power BI can display forecast data and support analytical calculations, but the reliability of future demand estimates depends on the forecasting method, data quality, and business assumptions used to generate them.

15. Selecting the Right Power BI Visuals

Choose visuals according to the operational question. Avoid adding too many charts to one page. Use tooltips, drill-through, and separate report pages for detailed analysis.

16. Making Dashboards Interactive

16.1 Adding Slicers

Slicers allow users to filter inventory and supply chain information by relevant dimensions.

Common slicers include:

• Date or Reporting Period.
• Product Category.
• Product Name or Product ID.
• Warehouse.
• Supplier.
• Region.
• Purchase Order Status.
• Delivery Status.

16.2 Drill-Down

Drill-down enables users to move through a hierarchy, such as year to quarter to month or product category to product. It helps identify when and where inventory or delivery performance changes occur.

16.3 Drill-Through

Drill-through opens a detailed page filtered to a selected product, warehouse, or supplier. For example, a user can select a supplier from a performance chart and open a detail page containing purchase orders, lead times, delivery dates, and outstanding quantities.

16.4 Tooltips

Tooltips display additional information when users hover over a visual. An inventory tooltip might display stock on hand, reorder point, stock value, recent demand, and expected incoming supply.

16.5 Conditional Formatting

Conditional formatting can highlight stock levels or delivery performance that require attention.

• Highlight stock below reorder point.
• Highlight overdue purchase orders.
• Flag products with negative inventory balances for investigation.
• Identify suppliers with low on-time delivery performance.
• Highlight warehouses exceeding capacity thresholds.

Use consistent formatting and provide labels or symbols in addition to colors where appropriate.

17. Demand and Supply Analysis

17.1 Understanding Demand

Demand represents the quantity of products or materials required over a defined period. It can come from customer orders, sales history, production requirements, or forecasts.

Demand analysis helps organizations:

• Identify seasonal patterns.
• Estimate future replenishment requirements.
• Recognize increasing or decreasing demand.
• Reduce the risk of shortages.
• Plan purchasing and production activities.

17.2 Understanding Supply

Supply includes inventory on hand and incoming quantities that can be used to meet demand, depending on their status and availability.

Examples include:

• Available physical stock.
• Confirmed purchase orders.
• Production orders expected to be completed.
• Approved transfers from other warehouses.

Not all incoming stock should be treated as immediately available. Expected delivery dates, reservations, quality holds, and order status should be considered.

17.3 Supply-Demand Balance

A simplified supply-demand balance can be expressed as:

Projected Stock =
Available Inventory
    + Expected Incoming Supply
    - Expected Demand

This calculation is useful for preliminary analysis but must account for the timing of receipts and demand. Stock may be sufficient over an entire month while still running out before a late delivery arrives.

17.4 Forecast Accuracy

Forecast accuracy evaluates how closely predicted demand matches actual demand. The selected method should be documented and suitable for the data.

Common approaches include mean absolute error (MAE), mean absolute percentage error (MAPE), and weighted error measures. MAPE can be misleading or undefined when actual demand is zero or very small, so it should not be used without considering these limitations.

18. Inventory Valuation and Cost Analysis

18.1 Understanding Inventory Value

Inventory value estimates the financial value of stock held by the organization. A simple operational estimate is the quantity of inventory multiplied by its unit cost.

Inventory Value =
Inventory Quantity * Unit Cost

Actual accounting valuation may depend on cost-flow assumptions, landed costs, write-downs, currency conversion, and other accounting rules. Use the approved valuation method when reporting official inventory value.

18.2 Inventory Holding Cost

Inventory holding costs are the costs associated with storing and maintaining inventory over time. Depending on the organization's definition, they may include:

• Warehouse rent and utilities.
• Insurance and handling.
• Capital or financing costs.
• Obsolescence and deterioration.
• Security and storage operations.

When a suitable holding cost field is available, Power BI can summarize it by product, category, warehouse, or reporting period.

18.3 Transportation Cost Analysis

Transportation cost analysis compares shipping expenses across carriers, routes, regions, and product categories.

Useful measures include:

• Total Transportation Cost.
• Average Cost per Shipment.
• Average Cost per Unit Delivered.
• Transportation Cost by Region.
• Cost of Late Deliveries, when measurable.

Comparisons should account for shipment distance, weight, volume, service level, and delivery requirements where relevant. Lower transportation cost does not always mean better service.

19. Inventory and Supply Chain Reconciliation

19.1 Why Reconciliation Matters

Inventory dashboards must agree with trusted operational systems when the same products, locations, dates, statuses, and business rules are used. Differences may occur because of delayed transactions, timing, unposted receipts, transfers, reservations, or data quality problems.

19.2 Inventory Reconciliation Checklist

• Compare stock balances with the inventory management system.
• Check the reporting date and snapshot timestamp.
• Confirm that all relevant warehouses are included.
• Validate receipts, issues, transfers, and adjustments.
• Investigate negative stock balances.
• Check for duplicated inventory snapshots.
• Validate unit-of-measure conversions.
• Confirm the treatment of reserved, damaged, or quarantined stock.

19.3 Supplier and Purchase Order Validation

• Compare purchase order totals with the procurement system.
• Validate received quantities against goods receipt records.
• Confirm that expected and actual delivery dates are valid.
• Check whether cancelled orders are excluded appropriately.
• Validate partial delivery handling.
• Confirm that on-time delivery metrics use the approved denominator.

20. Performance Optimization

20.1 Why Performance Matters

Inventory and supply chain datasets can contain millions of transactions across many products, locations, and reporting periods. An inefficient data model or excessive report interactions can make dashboards slow and difficult to use.

20.2 Performance Best Practices

• Remove unnecessary columns from imported data.
• Filter irrelevant historical data when business rules permit.
• Use an appropriate star schema.
• Avoid unnecessary many-to-many relationships.
• Use measures for aggregations that need to respond to report filters.
• Limit high-cardinality columns when they are not required.
• Review query folding in Power Query where supported.
• Use Performance Analyzer to investigate slow visuals.
• Consider incremental refresh for suitable large datasets.
• Reduce excessive visuals and complex interactions.

20.3 Optimizing DAX Measures

Test measures with realistic datasets and representative filters. Avoid iterating over large tables unnecessarily when a simpler aggregation is appropriate. Use variables where they improve clarity or avoid repeated calculations, and validate performance changes before deployment.

21. Security and Governance

21.1 Managing Dashboard Access

Inventory and supply chain reports may contain commercially sensitive supplier prices, procurement agreements, stock valuations, and customer order information. Access should be controlled according to organizational responsibilities.

• Assign suitable workspace and report permissions.
• Use row-level security when users should see only authorized regions or locations.
• Test access using appropriate user accounts.
• Review permissions regularly.
• Protect exported files and sensitive datasets.
• Use approved data-sharing procedures.

21.2 Data Governance

Data governance establishes standards for data ownership, quality, definitions, access, and maintenance.

Recommended governance practices include:

• Define ownership of important inventory KPIs.
• Document stock status and reorder rules.
• Maintain a data dictionary.
• Identify trusted sources for stock and purchase order information.
• Document inventory valuation assumptions.
• Maintain a record of changes to business logic.
• Establish a controlled testing and publishing process.

22. Publishing and Maintaining Dashboards

22.1 Publishing to Power BI Service

1. Validate the data model and calculations in Power BI Desktop.
2. Check that report filters and visuals work correctly.
3. Review data-source credentials and connection settings.
4. Publish the report to the appropriate workspace.
5. Configure gateway settings when required.
6. Set refresh schedules according to business needs.
7. Configure permissions and security rules.
8. Test the published report.
9. Document the release and communicate relevant changes.

22.2 Refresh Monitoring

Refresh monitoring helps ensure that inventory and supply chain reports display sufficiently current information.

Monitor the following:

• Refresh success and failure status.
• Refresh duration.
• Source system availability.
• Gateway health, where applicable.
• Credentials and permissions.
• Data freshness against reporting requirements.
• Unexpected changes in inventory balances.

22.3 Ongoing Maintenance Checklist

• Review critical refreshes.
• Investigate unexpected stock changes.
• Validate stock and supplier KPIs.
• Check report performance.
• Review access permissions.
• Update the data dictionary when fields change.
• Document changes to measures and thresholds.
• Test changes before publishing them.
• Collect feedback from inventory and supply chain teams.

23. Practical Project: Building an Inventory and Supply Chain Dashboard

23.1 Project Scenario

A retail organization manages several warehouses and purchases products from multiple suppliers. Management wants a Power BI dashboard to monitor inventory levels, replenishment needs, supplier delivery performance, and order fulfillment.

23.2 Step 1: Prepare the Dataset

Create or obtain sample datasets containing the following fields:

• Product ID and Product Name.
• Product Category.
• Warehouse ID and Warehouse Name.
• Inventory Snapshot Date.
• Opening Stock and Closing Stock.
• Received Quantity and Issued Quantity.
• Unit Cost.
• Reorder Level and Safety Stock.
• Supplier ID and Supplier Name.
• Purchase Order ID.
• Order Date and Expected Delivery Date.
• Actual Delivery Date.
• Ordered Quantity and Received Quantity.

23.3 Step 2: Clean Data Using Power Query

Standardize field names, set correct data types, investigate missing values, validate identifiers, and remove only confirmed duplicate records. Ensure the inventory snapshot and transaction tables have clearly defined grains.

23.4 Step 3: Build the Data Model

Create suitable fact tables for inventory snapshots, inventory movements, purchase orders, and sales. Create dimensions for products, suppliers, warehouses, and dates. Establish valid relationships and confirm that totals are not duplicated by joins or filter paths.

23.5 Step 4: Create DAX Measures

Total Closing Stock =
SUM(FactInventorySnapshot[ClosingStock])

Total Incoming Stock =
SUM(FactInventoryMovement[ReceivedQty])

Total Outgoing Stock =
SUM(FactInventoryMovement[IssuedQty])

Inventory Value =
SUMX(
    FactInventorySnapshot,
    FactInventorySnapshot[ClosingStock]
        * FactInventorySnapshot[UnitCost]
)

Average Supplier Lead Time =
AVERAGE(FactPurchaseOrders[LeadTimeDays])

Total Purchase Order Value =
SUM(FactPurchaseOrders[PurchaseOrderValue])

Adapt the table and column names to the actual model. Validate the grain and business definitions of all measures before using them in a report.

23.6 Step 5: Design the Dashboard Pages

1. Executive Overview: Inventory value, stockouts, purchase order value, and supplier delivery performance.
2. Inventory Analysis: Stock by product, category, and warehouse.
3. Replenishment Analysis: Products below reorder thresholds and outstanding supply.
4. Supplier Performance: Lead time, on-time delivery, and purchasing trends.
5. Warehouse Analysis: Stock distribution, movement, and capacity utilization.
6. Demand Analysis: Historical demand, forecast comparisons, and potential shortages.

23.7 Step 6: Add Interactive Features

Add slicers for date, product category, warehouse, and supplier. Configure drill-through pages for product and supplier details. Add conditional formatting to highlight low-stock items and overdue purchase orders.

23.8 Step 7: Validate the Dashboard

• Compare stock totals with the source inventory system.
• Validate inventory value calculations.
• Check the reorder logic for different warehouses.
• Verify supplier lead time calculations.
• Test on-time delivery metrics using eligible records only.
• Check filter behavior across all report pages.
• Review access permissions and refresh settings.

23.9 Expected Outcome

The completed dashboard should provide clear inventory visibility, highlight potential shortages, identify excess stock, monitor supplier performance, and support data-driven replenishment and supply chain decisions.

24. Common Mistakes to Avoid

25. Best Practices for Inventory and Supply Chain Dashboards

• Define the business purpose of each dashboard page.
• Use consistent naming conventions for tables, columns, and measures.
• Keep inventory snapshot and movement data separate when their grains differ.
• Use approved inventory valuation and stock availability rules.
• Define supplier lead time and on-time delivery consistently.
• Monitor stockouts, excess inventory, and replenishment requirements.
• Compare demand and supply using compatible time periods.
• Use meaningful slicers, drill-through, and conditional formatting.
• Validate KPIs against trusted operational systems.
• Document business definitions, assumptions, and thresholds.
• Apply suitable access controls to sensitive operational information.
• Optimize data models and DAX measures.
• Monitor refresh reliability and dashboard performance.
• Use version control and controlled release procedures for team projects.
• Collect feedback from warehouse, procurement, and supply chain teams.

26. Exercises and Assignments

Exercise 1: Inventory KPI Analysis

Create a Power BI report displaying total stock, inventory value, stockout products, and products below reorder level. Explain how each KPI can help a warehouse manager.

Exercise 2: Inventory Movement Analysis

Build a report showing opening stock, incoming quantities, outgoing quantities, and closing stock by month and warehouse. Investigate differences between calculated and recorded closing balances.

Exercise 3: Reorder Planning

Build a product-level table showing available stock, reorder point, safety stock, and open purchase order quantity. Identify products requiring replenishment and explain the assumptions used.

Exercise 4: Supplier Performance

Create a supplier dashboard showing average lead time, on-time delivery rate, purchase order value, and outstanding orders. Explain how you would evaluate suppliers fairly.

Exercise 5: Demand and Supply Analysis

Compare actual demand with forecast demand and expected supply. Identify products that may experience shortages and explain which additional data would improve the analysis.

Exercise 6: Dashboard Optimization

Use Performance Analyzer to investigate slow report visuals. Document the causes and explain the changes made to improve performance.

27. Interview Questions and Answers

Q1. What is an inventory dashboard?

Answer: An inventory dashboard displays stock quantities, inventory value, stock movements, reorder requirements, stockouts, and related metrics in an interactive report.

Q2. What is a supply chain dashboard?

Answer: A supply chain dashboard monitors procurement, suppliers, inventory, warehousing, transportation, and order fulfillment to improve operational visibility.

Q3. What is inventory turnover?

Answer: Inventory turnover measures how frequently inventory is sold or consumed relative to the average inventory held during a period. A common financial formula divides cost of goods sold by average inventory value.

Q4. What is a reorder point?

Answer: A reorder point is a stock threshold that signals when replenishment should be initiated. It often considers expected demand during lead time and safety stock.

Q5. What is safety stock?

Answer: Safety stock is additional inventory maintained to reduce the risk of shortages caused by uncertain demand or supply delays.

Q6. How do you calculate inventory value in Power BI?

Answer: A simple estimate can multiply stock quantity by unit cost and aggregate the result using SUMX. Official valuation must follow the organization's approved accounting method.

Q7. What is supplier lead time?

Answer: Supplier lead time is the time between a defined ordering event and the corresponding receipt event. It should be calculated using consistent dates and business rules.

Q8. What is on-time delivery percentage?

Answer: It is the number of eligible deliveries received on or before the agreed delivery date divided by the total eligible deliveries.

Q9. Why is data modeling important for inventory dashboards?

Answer: Data modeling establishes correct relationships between inventory, products, warehouses, suppliers, and dates. It helps prevent duplicated totals and improves report performance.

Q10. What is the purpose of Power Query?

Answer: Power Query connects to data sources, cleans records, standardizes fields, changes data types, and prepares data for the Power BI semantic model.

Q11. How can Power BI help prevent stockouts?

Answer: Power BI can highlight stock below reorder thresholds, compare available stock with demand, monitor expected receipts, and identify products at risk of running out.

Q12. How do you maintain a supply chain dashboard?

Answer: Maintenance includes monitoring refreshes, validating KPIs, reconciling source data, reviewing permissions, optimizing performance, and documenting changes.

28. Quick Revision Checklist

• Understand inventory management and supply chain processes.
• Identify reliable inventory, procurement, supplier, and logistics data sources.
• Clean and transform operational data using Power Query.
• Define the grain of inventory snapshots and transaction tables.
• Build an appropriate fact-and-dimension data model.
• Create DAX measures for stock, inventory value, and movement.
• Calculate inventory turnover and days of inventory on hand correctly.
• Monitor reorder points, safety stock, and stockout risk.
• Analyze supplier lead time and on-time delivery.
• Compare demand with available and incoming supply.
• Use appropriate charts, slicers, drill-through, and conditional formatting.
• Reconcile inventory and purchasing totals with trusted systems.
• Apply security and data governance practices.
• Monitor refreshes and report performance.
• Document calculations, assumptions, and maintenance procedures.

29. Conclusion

Building inventory and supply chain dashboards in Microsoft Power BI requires a combination of operational knowledge, data preparation, data modeling, DAX calculations, and effective visualization. Inventory dashboards help organizations monitor stock availability, inventory value, replenishment requirements, and stock movement. Supply chain dashboards extend this visibility to suppliers, procurement, logistics, and order fulfillment.

Reliable reporting depends on accurate source data, clearly defined KPIs, correct data grain, suitable relationships, and validation against trusted operational systems. Interactive filters, drill-through pages, and meaningful alerts allow users to investigate problems and respond to changing business conditions.

By applying the techniques covered in these notes, learners can build maintainable Power BI dashboards that support inventory control, supply chain performance analysis, and better operational decision-making.

30. Additional Learning Resources

• Microsoft Power BI Training at JustAcademy
• Register for a Power BI Course Demo
• JustAcademy Official Website
• Microsoft Power BI Documentation
• Microsoft DAX Documentation
• Microsoft Power Query Documentation
• Microsoft Inventory Visibility Power BI Dashboard Guide
• Microsoft Inventory Power BI App Documentation